<?php
/**
 * 物件問合せ関連モデル<br />
 *
 * @package apamanshop
 * @author K.Ikenaga
 * @copyright
 */
class Inquiry_Model_BukkenEntry extends Inquiry_Model_Base
{
	/**
	 * キャンペーン情報を取得する。
	 *
	 * @param string $strCampaignKbn キャンペーン区分(必須)(複数の場合はカンマ区切り)
	 * @param int $intInquiryKbn 問い合わせ区分（0=物件問い合わせ、1=物件リクエスト、2=店舗問い合わせ）
	 * @param string $strMobileKbn モバイル区分
	 * @return array キャンペーン情報
	 */
	public function GetCampaignEx($strCampaignKbn, $intInquiryKbn=null, $strMobileKbn='0')
	{
		$sql = "";
		$db_params = array();
		
		$sql .= "SELECT ";
		$sql .= "   M94_CAMPAIGN_CD ";
		$sql .= "  ,M94_CAMPAIGN_NAME ";
		$sql .= "  ,M94_START_YMD ";
		$sql .= "  ,M94_END_YMD ";
		$sql .= "  ,M94_CAMPAIGN_RYAKUSHOU ";
		$sql .= "  ,M94_CAMPAIGN_SETUMEI ";
		$sql .= "  ,M94_CAMPAIGN_KBN ";
		$sql .= "  ,M94_CAMPAIGN_LIST_FLAG ";
		$sql .= "  ,M94_CAMPAIGN_HYOUZI ";
		$sql .= "  ,M94_CAMPAIGN_RANK ";
		$sql .= "  ,M94_CAMPAIGN_PRIORITY_NO ";
		$sql .= "  ,M94_CAMPAIGN_URL ";
		$sql .= "  ,M94_CAMPAIGN_GAZOU ";
		$sql .= "  ,M94_CAMPAIGN_PR_SEARCH ";
		$sql .= "  ,M94_CAMPAIGN_NAME_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_LIST_FLAG_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_HYOUZI_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_URL_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_GAZOU_MOBILE ";
		$sql .= "  ,M94_WEBCALLING_TYPE ";
		$sql .= "  ,M94_WEBCALLING_PRIORITY ";
		$sql .= "  ,M94_CAMPAIGN_TOKUSYU_KBN ";
		$sql .= "  ,M94_DEFAULT_CHECK_CD ";
		$sql .= "  ,M94_BUKKEN_INQ_FLG ";
		$sql .= "  ,M94_REQUEST_INQ_FLG ";
		$sql .= "  ,M94_SHOP_INQ_FLG ";
		$sql .= "FROM ";
		$sql .= "  ASNFC_USER.M94CAMPAIGN ";
		
		//===条件===
		$sql .= "WHERE M94_DELETE_FLAG = 0 ";
		
		//モバイル区分
		if (!Apm_Otheritems::IsNullOrEmpty($strMobileKbn))
		{
			$sql .= " AND M94_MOBILE_KBN = :mobileKbn ";
			$db_params["mobileKbn"] = $strMobileKbn;
		}
		
		//キャンペーン区分
		if (!Apm_Otheritems::IsNullOrEmpty($strCampaignKbn))
		{
			$sql .= " AND M94_CAMPAIGN_KBN IN (" . $strCampaignKbn . ") ";
		}
		
		//有効期間(開始がnullの場合は無効とする。終了がnullの場合はエンドレスとする)
		$sql .= "  AND :nowDate BETWEEN NVL(M94_START_YMD, '99999999') AND NVL(M94_END_YMD, '99999999') ";
		$db_params["nowDate"] = date("Ymd");
		
		//問い合わせ区分
		if (!is_null($intInquiryKbn))
		{
			switch($intInquiryKbn) {
				case 0:
					$sql .= " AND M94_BUKKEN_INQ_FLG = '1'";
					break;
				case 1:
					$sql .= " AND M94_REQUEST_INQ_FLG = '1'";
					break;
				case 2:
					$sql .= " AND M94_SHOP_INQ_FLG = '1'";
					break;
			}
		}
		
		//===並び順===
		$sql .= "ORDER BY ";
		$sql .= "   nvl(M94_CAMPAIGN_RANK,0) ASC ";
		$sql .= "  ,nvl(M94_CAMPAIGN_PRIORITY_NO,0) ASC ";
		$sql .= "  ,M94_START_YMD ";
		
		$result = $this->dbGetList($sql, $db_params);
		return $result;
	}

	/**
	 * キャンペーン情報を取得する
	 *
	 * @param int $strCampaignKbn キャンペーン区分(必須)(複数の場合はカンマ区切り)
	 * @param int $intCampaignCd キャンペーンコード
	 * @return array キャンペーン情報リスト
	 */
	public function GetCampaign($strCampaignKbn, $intCampaignCd = null)
	{
		$sql = "";
		$db_params = array();

		$sql .= "SELECT ";
		$sql .= "   M94_CAMPAIGN_CD ";
		$sql .= "  ,M94_CAMPAIGN_NAME ";
		$sql .= "  ,M94_START_YMD ";
		$sql .= "  ,M94_END_YMD ";
		$sql .= "  ,M94_CAMPAIGN_RYAKUSHOU ";
		$sql .= "  ,M94_CAMPAIGN_SETUMEI ";
		$sql .= "  ,M94_CAMPAIGN_KBN ";
		$sql .= "  ,M94_CAMPAIGN_LIST_FLAG ";
		$sql .= "  ,M94_CAMPAIGN_HYOUZI ";
		$sql .= "  ,M94_CAMPAIGN_RANK ";
		$sql .= "  ,M94_CAMPAIGN_PRIORITY_NO ";
		$sql .= "  ,M94_CAMPAIGN_URL ";
		$sql .= "  ,M94_CAMPAIGN_GAZOU ";
		$sql .= "  ,M94_CAMPAIGN_PR_SEARCH ";
		$sql .= "  ,M94_CAMPAIGN_NAME_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_LIST_FLAG_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_HYOUZI_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_URL_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_GAZOU_MOBILE ";
		$sql .= "  ,M94_TOUROKU_YMD ";
		$sql .= "  ,M94_DELETE_FLAG ";
		$sql .= "  ,M94_UPDATE_YMD ";
		$sql .= "FROM ";
		$sql .= "  ASNFC_USER.M94CAMPAIGN ";

		//===条件===
		$sql .= "WHERE M94_DELETE_FLAG = 0 ";
		$sql .= "AND M94_MOBILE_KBN = 0 ";

		//キャンペーン区分
		if (!Apm_Otheritems::IsNullOrEmpty($strCampaignKbn))
		{
			$sql .= " AND M94_CAMPAIGN_KBN IN (" . $strCampaignKbn . ") ";

		}

		//有効期間(開始がnullの場合は無効とする。終了がnullの場合はエンドレスとする)
		$sql .= "  AND :nowDate BETWEEN NVL(M94_START_YMD, '99999999') AND NVL(M94_END_YMD, '99999999') ";
		$db_params["nowDate"] = date("Ymd");

		//キャンペーンコード
		if (!is_null($intCampaignCd))
		{
			$sql .= " AND M94_CAMPAIGN_CD = :campaignCd ";
			$db_params["campaignCd"] = $intCampaignCd;
		}

		//===並び順===
		$sql .= "ORDER BY ";
		$sql .= "   nvl(M94_CAMPAIGN_RANK,0) ASC ";
		$sql .= "  ,nvl(M94_CAMPAIGN_PRIORITY_NO,0) ASC ";
		$sql .= "  ,M94_START_YMD ";

		$result = $this->dbGetList($sql, $db_params);
		return $result;

	}

	/**
	 * キャンペーン情報リストを取得する（ss-sakakibara）
	 *
	 * @param int $strCampaignKbn キャンペーン区分(必須)(複数の場合はカンマ区切り)
	 * @param int[] $intCampaignCdList キャンペーンコードリスト
	 * @return array キャンペーン情報リスト
	 */
	public function GetCampaignList($strCampaignKbn, $intCampaignCdList = null)
	{

		$sql = "";
		$db_params = array();

		//駅CDをカンマ区切りにする
		$campaignCd='';
		$index = 0;
		foreach ($intCampaignCdList as $value)
		{
			$campaignCd .= ' :campaignCd_'.$index.', ';
			$db_params['campaignCd_'.$index] = $value;
			$index++;
		}
		$campaignCd = substr($campaignCd,0,strlen($campaignCd)-2);

		$sql .= "SELECT ";
		$sql .= "   M94_CAMPAIGN_CD ";
		$sql .= "  ,M94_CAMPAIGN_NAME ";
		$sql .= "  ,M94_START_YMD ";
		$sql .= "  ,M94_END_YMD ";
		$sql .= "  ,M94_CAMPAIGN_RYAKUSHOU ";
		$sql .= "  ,M94_CAMPAIGN_SETUMEI ";
		$sql .= "  ,M94_CAMPAIGN_KBN ";
		$sql .= "  ,M94_CAMPAIGN_LIST_FLAG ";
		$sql .= "  ,M94_CAMPAIGN_HYOUZI ";
		$sql .= "  ,M94_CAMPAIGN_RANK ";
		$sql .= "  ,M94_CAMPAIGN_PRIORITY_NO ";
		$sql .= "  ,M94_CAMPAIGN_URL ";
		$sql .= "  ,M94_CAMPAIGN_GAZOU ";
		$sql .= "  ,M94_CAMPAIGN_PR_SEARCH ";
		$sql .= "  ,M94_CAMPAIGN_NAME_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_LIST_FLAG_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_HYOUZI_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_URL_MOBILE ";
		$sql .= "  ,M94_CAMPAIGN_GAZOU_MOBILE ";
		$sql .= "  ,M94_TOUROKU_YMD ";
		$sql .= "  ,M94_DELETE_FLAG ";
		$sql .= "  ,M94_UPDATE_YMD ";
		$sql .= "FROM ";
		$sql .= "  ASNFC_USER.M94CAMPAIGN ";

		//===条件===
		$sql .= "WHERE M94_DELETE_FLAG = 0 ";
		$sql .= "AND M94_MOBILE_KBN = 0 ";

		//キャンペーン区分
		if (!Apm_Otheritems::IsNullOrEmpty($strCampaignKbn))
		{
			$sql .= " AND M94_CAMPAIGN_KBN IN (" . $strCampaignKbn . ") ";

		}

		//有効期間(開始がnullの場合は無効とする。終了がnullの場合はエンドレスとする)
		$sql .= "  AND :nowDate BETWEEN NVL(M94_START_YMD, '99999999') AND NVL(M94_END_YMD, '99999999') ";
		$db_params["nowDate"] = date("Ymd");

		//キャンペーンコード
		if (is_array($intCampaignCdList) && count($intCampaignCdList) > 0 )
		{
			$sql .= " AND M94_CAMPAIGN_CD IN (".$campaignCd.') ';
		}

		//===並び順===
		$sql .= "ORDER BY ";
		$sql .= "   nvl(M94_CAMPAIGN_RANK,0) ASC ";
		$sql .= "  ,nvl(M94_CAMPAIGN_PRIORITY_NO,0) ASC ";
		$sql .= "  ,M94_START_YMD ";

		$result = $this->dbGetList($sql, $db_params);
		return $result;

	}


	/**
	 * 物件問合せ物件情報記載データ作成
	 * @param unknown_type $value
	 */
	public function convertBukkenList($value)
	{
		$result_tmp = array();

		foreach($value as $key => $val){

			$tmp = array();
			$tmp['DspAddress'] ="";
			$tmp['DspEnsen'] = "";
			$tmp['DspTinryo'] ="";
			$tmp['DspKyoueki'] ="";
			$tmp['DspShiki'] ="";
			$tmp['DspRei'] ="";
			$tmp['DspHosyo'] ="";
			$tmp['DspBaisyo'] ="";
			$tmp['DspMadori'] ="";
			$tmp['DspSenyu'] ="";
			$tmp['DspTiku'] ="";
			$tmp['DspTukiym'] ="";
			$tmp['DspShopName'] ="";

			//住所
			if($val['T03_JYUUSHO_TEXT'] != "") {
				$tmp['DspAddress'] = $val['T03_JYUUSHO_TEXT'];
			}else{
				$tmp['DspAddress'] = "-";
			}
			//沿線設定
			$tmp['DspEnsen'] = $this->convtraffic($val);

			$tmp['DspTinryo']  = Apm_Bukkenitems::GetChinryouStr($val);				// 賃料
			$tmp['DspKyoueki'] = Apm_Bukkenitems::GetKyouekihiStr($val,'1');		// 管理費・共益費
			$tmp['DspShiki']   = Apm_Bukkenitems::GetShikikinStr($val);				// 敷金
			$tmp['DspRei']     = Apm_Bukkenitems::GetReikinStr($val);				// 礼金
			$tmp['DspHosyo']   = Apm_Bukkenitems::GetHoshoukinStr($val);			// 保証金
			$tmp['DspBaisyo']  = Apm_Bukkenitems::GetShoukyakukinStr($val);			//敷引金・償却金
			$tmp['DspMadori']  = Apm_Bukkenitems::GetMadoriStr($val);				// 間取り
			$tmp['DspSenyu']   = $val['T03_SENYUU_MENSEKI'];						// 専有面積
			$tmp['DspTiku']    = Apm_Bukkenitems::GetBuildYStr($val);				// 築年数
			$tmp['DspTukiym']  = Apm_Bukkenitems::GetBuildYmStr($val);				// 築年月

			//店舗名
			$tmp['DspShopName'] = $val['T03_TENPO_NAME'];

			array_push($result_tmp,$tmp);
		}

		return $result_tmp;
	}

	/**
	 * NULL→０変換
	 * @param unknown_type $val
	 * @return number|unknown
	 */
	public function convNvl($val){

		if($val == ""){
			return 0;
		}else{
			return $val;
		}
	}

	/**
	 * 経路データ作成
	 * @param unknown_type $val
	 */
	public function convtraffic($val){
		//ｽﾃｰﾀｽ 初期化
		$strWork = "";

		$strTrainNm = $val['T03_ENSEN_NAME'];
		$strStationNm = $val['T03_EKI_NAME'];
		$strStationCd = $val['T03_ENSEN_CD01'];
		$strBusJyousha = $this->convNvl($val['T03_BUS_JYOUSHA']);
		$strBusStop = $val['T03_BUS_STOP'];
		$strToho = $this->convNvl($val['T03_TOHO01']);

		//駅名称が設定されている場合
		if (( $strStationNm != "" ) && ( $strStationNm != "＊＊＊＊" ) && ( $strStationNm != "＊＊＊" )) {

			$strWork .= $strTrainNm . "/" . $strStationNm;

			//ﾊﾞｽ乗車時間＞０であった場合
			if($strBusJyousha > 0) {
				$strWork .= " バス乗車" . $strBusJyousha . "分";

				//ﾊﾞｽ停名が設定されている場合
				if ($strBusStop != "") {
					$strWork .= "<br/> " . $strBusStop;
				}
				//徒歩分＞０であった場合
				if ($strToho > 0) {
					$strWork .= " 停歩";
				}

			//上記以外であった場合
			}else {
				//徒歩分＞０であった場合
				if ($strToho > 0) {
					$strWork .= " 徒歩";
				}
			}

			//@ 徒歩
			//徒歩分＞０であった場合
			if ($strToho > 0) {
				$strWork .=  " " . $strToho . "分";
			}

		//沿線がﾊﾞｽであった場合
		}else if ( ( $strStationCd == "3500" ) || ( $strStationCd == "3501" ) || ( $strStationCd == "3502" ) || ( $strStationCd == "3503" )
				|| ( $strStationCd == "3504" ) || ( $strStationCd == "3505" ) || ( $strStationCd == "3506" ) || ( $strStationCd == "3507" )
				|| ( $strStationCd == "3508" ) || ( $strStationCd == "3509" ) || ( $strStationCd == "3510" ) || ( $strStationCd == "3511" ))
		{
			$strWork .= $strTrainNm . "/"  . $strBusStop;
			//@ 徒歩
			//徒歩分＞０であった場合
			if(strToho > 0 ){
				$strWork = $strWork . " 徒歩" . $strToho . "分";
			}

		//上記以外であった場合
		}else{
			$strWork = $strWork . "－";
		}

		return $strWork;
	}

	/**
	 * 問合せ物件一覧取得
	 *
	 * @param string $bukkenIds 物件ID(複数の場合は,区切り)
	 *
	 * @return 問合せ物件一覧
	 */
	public function GetInqBukkenList($bukkenIds)
	{
		$sql = "";
		$db_params = array();

		$bukkenIdList;
		$bukkenIdQry;

		//========================================================
		//= 変数初期化

		$bukkenIdQry = "";
		$bukkenIdList = split(",", str_replace(" ", "", $bukkenIds));

		//========================================================
		//= ｸｴﾘ用文字列の作成

		//対象部屋ﾃﾞｰﾀ一覧の取得
		foreach ($bukkenIdList as $bukkenId)
		{
			if (!Apm_Otheritems::IsNullOrEmpty($bukkenIdQry))
			{
				$bukkenIdQry .= " OR ";
			}
			$bukkenIdQry .= "T03_HEYA_CD = " . $bukkenId . " ";
		}

		//========================================================
		//= ｸｴﾘ用作成

		$sql .= "SELECT ";
		$sql .= "  T03_ORIGINAL_TATEMONO_CD01, ";
		$sql .= "  T03_ORIGINAL_HEYA_CD01, ";
		$sql .= "  T03_TATEMONO_CD, ";
		$sql .= "  T03_HEYA_BANGOU, ";
		$sql .= "  T03_JYUUSHO_TEXT, ";
		$sql .= "  T03_TATEMONO_NAME, ";
		$sql .= "  T03_HEYA_CD, ";
		$sql .= "  T03_TODOUFUKEN_CD, ";
		$sql .= "  T03_TENPO_CD, T03_TENPO_NAME, ";
		$sql .= "  T03_ENSEN_NAME, T03_EKI_NAME, ";
		$sql .= "  T03_MADORI, ";
		$sql .= "  T03_YACHIN, T03_YACHIN_TAX, ";
		$sql .= "  T03_KANRIHI, T03_KANRIHI_TAX, ";
		$sql .= "  T03_REIKIN, T03_REIKIN_TSUKISUU, ";
		$sql .= "  T03_SHIKIKIN, T03_SHIKIKIN_TSUKISUU,  ";
		$sql .= "  T03_hoshoukin, T03_hoshoukin_tsukisuu, ";
		$sql .= "  T03_shoukyakukin, t03_shoukyakukin_tsukisuu, ";
		$sql .= "  T03_BUS_GAISHA, T03_BUS_STOP, T03_BUS_JYOUSHA, ";
		$sql .= "  T03_TOHO01,T03_SENYUU_MENSEKI, ";
		$sql .= "  T03_TATEMONO_KOUZOU, ";
		$sql .= "  T03_KANSEINEN, T03_KANSEITSUKI, ";
		$sql .= '  trunc(';
		$sql .= '         months_between(';
		$sql .= '                         sysdate,';
		$sql .= "                         to_date(lpad(TO_CHAR(NVL(T03_KANSEINEN,'00')),4,'0')||lpad(TO_CHAR(NVL(T03_KANSEITSUKI,'00')),2,'0')||lpad(TO_CHAR(NVL(T03_KANSEIBI,'01')),2,'0'), 'yyyy.mm.dd')";
		$sql .= '                       ) /12';
		$sql .= '       ) AS T03_TIKU_NENSU, ';
		$sql .= "  T03_GAIKAN_GAZOU01, T03_MADORIZU,";
		$sql .= "  T03_GAIKAN_GAZOU02, ";
		$sql .= "  T03_GAIKAN_GAZOU03, ";
		$sql .= "  T03_GAIKAN_GAZOU04, ";
		$sql .= "  T03_GAIKAN_GAZOU05, ";
		$sql .= "  T03_HAICHIZU, ";
		$sql .= "  T03_SHUHEN_GAZOU01, ";
		$sql .= "  T03_SHUHEN_GAZOU02, ";
		$sql .= "  T03_NAISOU_GAZOU01, ";
		$sql .= "  T03_NAISOU_GAZOU02, ";
		$sql .= "  T03_NAISOU_GAZOU03, ";
		$sql .= "  T03_NAISOU_GAZOU04, ";
		$sql .= "  T03_NAISOU_GAZOU05, ";
		$sql .= "  T03_NAISOU_GAZOU06, ";
		$sql .= "  T03_NAISOU_GAZOU07, ";
		$sql .= "  T03_NAISOU_GAZOU08, ";
		$sql .= "  T03_NAISOU_GAZOU09, ";
		$sql .= "  T03_NAISOU_GAZOU10, ";
		$sql .= "  T03_PANORAMA_GAZOU01, ";
		$sql .= "  T03_PANORAMA_GAZOU02, ";
		$sql .= "  T03_MADORIZU_3D, ";
		$sql .= "  T03_PR01, ";
		$sql .= "  T03_PR02, ";
		$sql .= "  M05_TENPO_CD, ";
		$sql .= "  M05_TENPO_NAME, ";
		$sql .= "  M05_TENPO_TEL01, ";
		$sql .= "  M05_TENPO_MAIL, ";
		$sql .= "  T03_KOUKOKU_KAISHA_CD, ";
		$sql .= "  T03_KOUKOKU_TOIAWASE_NO, ";
		$sql .= "  T03_KASHIDASHI_KUBUN, ";
		$sql .= "  T03_CAMPAIGN_FLAG, ";
		$sql .= "  T03_CAMPAIGN2_FLAG, ";
		$sql .= "  T03_CAMPAIGN3_FLAG, ";
		$sql .= "  T03_CAMPAIGN4_FLAG, ";
		$sql .= "  T03_CAMPAIGN5_FLAG, ";
		$sql .= "  T03_CAMPAIGN6_FLAG, ";
		$sql .= "  T03_CAMPAIGN7_FLAG, ";
		$sql .= "  T03_CAMPAIGN8_FLAG, ";
		$sql .= "  T03_CAMPAIGN9_FLAG, ";
		$sql .= "  T03_CAMPAIGN10_FLAG, ";
		$sql .= "  T03_FREERENT_FLAG, ";
		$sql .= "  T03_HEYAWARI_FLAG, ";
		$sql .= "  M01_TODOUFUKEN, ";
		$sql .= "  M01_SHIKUCHOUSON ";
		$sql .= "FROM T03WEB_HEYA ,M05TENPO_INFO, M01JYUUSHO ";
		$sql .= "WHERE ( " . $bukkenIdQry . " ) ";
		$sql .= "AND M05_TENPO_CD(+) = T03_TENPO_CD ";
		$sql .= "AND M01_TODOUFUKEN_CD(+) = T03_TODOUFUKEN_CD ";
		$sql .= "AND M01_SHIKUCHOUSON_CD(+) = T03_SHIKUCHOUSON_CD ";
		$sql .= "AND M01_CHOUMEI_CD(+) = T03_CHOUMEI_CD ";
		$sql .= "AND M01_CHOUMOKU_CD(+) = T03_CHOUMOKU_CD ";
		$sql .= "ORDER BY T03_TENPO_CD ";

		$result = $this->dbGetList($sql, $db_params);
		return $result;
	}

}
?>
